use serde::{Deserialize, Serialize};
use sqlx::{Execute, FromRow, Pool, QueryBuilder, Sqlite};
use sqlx::sqlite::SqliteQueryResult;
use tracing::{error, info};

const PICTURE_DDL: &str = r#"
create table if not exists picture
(
    src    text,
    name   text,
    width  integer,
    height integer,
    size   real,
    tags   text,
    constraint picture_pk
        primary key (src, name)
);
"#;

#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct PictureModel {
    pub src: String,
    pub name: String,
    pub width: i64,
    pub height: i64,
    pub size: f64,
    pub tags: Option<String>,
    pub deleted: bool,
}

impl PictureModel {
    pub async fn select_all(db: &Pool<Sqlite>) -> Vec<Self> {
        let mut qb = QueryBuilder::new("select * from picture;");
        match qb.build().fetch_all(db).await {
            Ok(qs) => qs.iter()
                .filter_map(|x| Self::from_row(x).ok())
                .collect::<Vec<Self>>(),
            Err(err) => {
                error!("{}", err);
                vec![]
            }
        }
    }

    pub async fn insert_batch(db: &Pool<Sqlite>, entities: &[Self], batch_size: usize) -> anyhow::Result<SqliteQueryResult> {
        let mut offset = 0;
        let mut res_arr: Vec<SqliteQueryResult> = Vec::new();
        while offset < entities.len() {
            let mut tail = offset + batch_size;
            if tail >= entities.len() { tail = entities.len(); }
            let batch = &entities[offset..tail];

            let mut qb: QueryBuilder<Sqlite> = QueryBuilder::new("insert into picture(src, name, width, height, size, tags, deleted) ");
            qb.push_values(batch.iter(), |mut e, rec| {
                e.push_bind(&rec.src)
                    .push_bind(&rec.name)
                    .push_bind(rec.width)
                    .push_bind(rec.height)
                    .push_bind(rec.size)
                    .push_bind(&rec.tags)
                    .push_bind(false);
            });

            let query = qb.build();
            info!("{}", query.sql());
            let query_res = query.execute(db).await?;
            res_arr.push(query_res);
            offset += batch_size;
        }
        let mut res = SqliteQueryResult::default();
        res.extend(res_arr);
        Ok(res)
    }
}